import requests
from requests.packages import urllib3
import json
import re
import pandas as pd
from lxml import etree
import collections
import pymysql

# 追踪基金持仓部位
with open('../config/mysql.json','r',encoding='utf8')as fp:
    json_data = json.load(fp)
    db = pymysql.connect(json_data["url"], json_data["user"], json_data["pwd"], json_data["database"])
def fund_code_name():
    """ 筛选天天基金，6千多基金机构的，成立以来收益率排在前50强基金"""
    header = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36',
        'Referer': 'http://fund.eastmoney.com/data/fundranking.html',
        'Cookie': 'st_si=51694067779834; st_asi=delete; ASP.NET_SessionId=e1pno0koqkcp5es3xyzyrg1n; EMFUND1=null; EMFUND2=null; EMFUND3=null; EMFUND4=null; EMFUND5=null; EMFUND6=null; EMFUND7=null; EMFUND8=null; EMFUND0=null; _adsame_fullscreen_18503=1; EMFUND9=08-16 01:16:38@#$%u4E07%u5BB6%u65B0%u5229%u7075%u6D3B%u914D%u7F6E%u6DF7%u5408@%23%24519191; st_pvi=87492384111747; st_sp=2020-08-16%2000%3A05%3A17; st_inirUrl=http%3A%2F%2Ffund.eastmoney.com%2Fdata%2Ffundranking.html; st_sn=15; st_psi=20200816011636912-0-9218336114'

    }
    response = requests.get(
        url='http://fund.eastmoney.com/data/rankhandler.aspx?op=ph&dt=kf&ft=all&rs=&gs=0&sc=lnzf&st=desc&sd=2019-10-20&ed=2020-10-20&qdii=&tabSubtype=,,,,,&pi=1&pn=50&dx=1&v=0.38728430938715985', headers=header
    )
    text = response.text
    data = text.split("=")[1]
    compile_data = re.findall("{datas:\\[(.*)\\],allRecords", str(data))[0]
    strip_data = str(compile_data).strip('[').strip(']')
    replace_data = strip_data.replace('"', "")
    quota_arrays = replace_data.split(",")
    intervals = [[i * 25, (i + 1) * 25] for i in range(258)]
    narrays = []
    for k in intervals:
        start, end = k[0], k[1]
        line = quota_arrays[start:end]
        narrays.append(line)
    header_data = ["基金代码", "基金简称", "基金条码", "日期",
                   "单位净值", "累计净值", "日增长率", "近1周增长率", "近1月增长率", "近3月", "近半年", "近1年", "近2年", "近3年",
                   "今年来", "成立来", "其他1", "其他2", "其他3", "其他4", "其他5", "其他6", "其他7", "其他8", "其他9"]
    df = pd.DataFrame(narrays, columns=header_data)
    df_part = df[["基金代码", "基金简称", "日期",
                  "单位净值", "累计净值", "日增长率", "近1周增长率", "近1月增长率", "近3月", "近半年","近1年", "近2年", "近3年",
                   "今年来", "成立来"]]
    print("<" * 30,"成立来最强增长基金",">" * 30)
    df_tmp = df_part.sort_values(by=['成立来'], ascending=False, axis=0)
    rank_fund_code = df_tmp.head(50)['基金代码']
    fund_codes_list = rank_fund_code.values.tolist()
    print("前50强基金：", fund_codes_list)
    df_tmp.head(50).to_csv("./本季度前50强基金收益.csv", encoding="utf_8_sig")
    return fund_codes_list

def fund_code_name_worst():
    """ 筛选天天基金，6千多基金机构的，3年以来收益率排在最后50强基金"""
    header = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36',
        'Referer': 'http://fund.eastmoney.com/data/fundranking.html',
        'Cookie': 'st_si=51694067779834; st_asi=delete; ASP.NET_SessionId=e1pno0koqkcp5es3xyzyrg1n; EMFUND1=null; EMFUND2=null; EMFUND3=null; EMFUND4=null; EMFUND5=null; EMFUND6=null; EMFUND7=null; EMFUND8=null; EMFUND0=null; _adsame_fullscreen_18503=1; EMFUND9=08-16 01:16:38@#$%u4E07%u5BB6%u65B0%u5229%u7075%u6D3B%u914D%u7F6E%u6DF7%u5408@%23%24519191; st_pvi=87492384111747; st_sp=2020-08-16%2000%3A05%3A17; st_inirUrl=http%3A%2F%2Ffund.eastmoney.com%2Fdata%2Ffundranking.html; st_sn=15; st_psi=20200816011636912-0-9218336114'

    }
    response = requests.get(
        url='http://fund.eastmoney.com/data/rankhandler.aspx?op=ph&dt=kf&ft=all&rs=&gs=0&sc=3nzf&st=asc&sd=2019-10-20&ed=2020-10-20&qdii=&tabSubtype=,,,,,&pi=1&pn=50&dx=1&v=0.030070152990213428', headers=header
    )
    text = response.text
    data = text.split("=")[1]
    compile_data = re.findall("{datas:\\[(.*)\\],allRecords", str(data))[0]
    strip_data = str(compile_data).strip('[').strip(']')
    replace_data = strip_data.replace('"', "")
    quota_arrays = replace_data.split(",")
    intervals = [[i * 25, (i + 1) * 25] for i in range(258)]
    narrays = []
    for k in intervals:
        start, end = k[0], k[1]
        line = quota_arrays[start:end]
        narrays.append(line)
    header_data = ["基金代码", "基金简称", "基金条码", "日期",
                   "单位净值", "累计净值", "日增长率", "近1周增长率", "近1月增长率", "近3月", "近半年", "近1年", "近2年", "近3年",
                   "今年来", "成立来", "其他1", "其他2", "其他3", "其他4", "其他5", "其他6", "其他7", "其他8", "其他9"]
    df = pd.DataFrame(narrays, columns=header_data)
    df_part = df[["基金代码", "基金简称", "日期",
                  "单位净值", "累计净值", "日增长率", "近1周增长率", "近1月增长率", "近3月", "近半年","近1年", "近2年", "近3年",
                   "今年来", "成立来"]]
    print("<" * 30, "成立来最差增长基金", ">" * 30)
    df_tmp = df_part.sort_values(by=['成立来'], ascending=False, axis=0)
    rank_fund_code = df_tmp.head(50)['基金代码']
    fund_codes_list = rank_fund_code.values.tolist()
    print("前50强基金：", fund_codes_list)
    df_tmp.head(50).to_csv("./后50强基金收益.csv", encoding="utf_8_sig")
    return fund_codes_list

def get_one_fund_stocks(fund_code):
    """根据基金码,获取每一支基金的最新一季度所有持仓股票池前10支股票"""
    url = "http://fundf10.eastmoney.com/FundArchivesDatas.aspx?type=jjcc&code={}&topline=10&year=&month=&rt=0.5032668912422176".format(
        fund_code)
    head = {
        "Cookie": "EMFUND1=null; EMFUND2=null; EMFUND3=null; EMFUND4=null; EMFUND5=null; EMFUND6=null; EMFUND7=null; EMFUND8=null; EMFUND0=null; st_si=44023331838789; st_asi=delete; EMFUND9=08-16 22:04:25@#$%u4E07%u5BB6%u65B0%u5229%u7075%u6D3B%u914D%u7F6E%u6DF7%u5408@%23%24519191; ASP.NET_SessionId=45qdofapdlm1hlgxapxuxhe1; st_pvi=87492384111747; st_sp=2020-08-16%2000%3A05%3A17; st_inirUrl=http%3A%2F%2Ffund.eastmoney.com%2Fdata%2Ffundranking.html; st_sn=12; st_psi=2020081622103685-0-6169905557"
        ,
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.125 Safari/537.36"}
    response = requests.get(url, headers=head)
    text = response.text
    div = re.findall('content:\\"(.*)\\",arryear', text)[0]
    html_body = '<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><title>test</title></head><body>%s</body></html>' % (
        div)
    html = etree.HTML(html_body)
    stock_info = html.xpath('//div[1]/div/table/tbody/tr/td/a')
    stock_money = html.xpath('//div[1]/div/table/tbody/tr/td')
    stock_one_fund = []
    for stock in stock_info:
        if stock.text and stock.text.isdigit():
            stock_one_fund.append(stock.text)
    if len(stock_one_fund) > 1:
        print("基金代码：{}".format(fund_code), "基金前十股票：", stock_one_fund)
    return stock_one_fund


def static_best_stock(rank=20, **kw):
    """ 统计收益最佳前50机构共同持有股票代码情况,修改rank数量可调整展示股票排名数目"""
    rank_codes = []
    if 'trace_list' in kw:
        for code in kw['trace_list']:
            rank_codes.append(code[1])
    elif 'query_list' in kw:
        rank_codes=fund_code_name_worst()
    else:
        rank_codes = fund_code_name()
    stocks_array = []
    for index, code in enumerate(rank_codes):
        if index < 1:
            print("<" * 30 + "FBI WARNING基金的排名高到低排序以及股票池情况" + ">" * 30)
        stocks = get_one_fund_stocks(code)
        if len(stocks) > 1 and stocks:
            stocks_array.extend(stocks)
    count_each_stock = collections.Counter(stocks_array)
    print("<" * 30 + "FBI WARNING,{}".format(static_best_stock.__doc__) + ">" * 30)
    print("#" * 30 + "本季度基金机构共同持有股票数目排行前{}股票代码情况".format(rank) + "#" * 30)
    df = pd.DataFrame.from_dict(count_each_stock, orient='index', columns=["持有该股机构数目"])
    df = df.reset_index().rename(columns={"index": "股票代码"})

    df = df.sort_values(by="持有该股机构数目", ascending=False)
    print(df.head(rank))
    return df.head(rank)


def fund_trace():
    # db = pymysql.connect("localhost", "root", "root", "lw")
    cursor = db.cursor()
    cursor.execute("select * from t_fund");
    fund_list = cursor.fetchall()
    if fund_list:
        df = static_best_stock(trace_list=fund_list)

        if not df.empty:
            cursor.execute("delete from t_fund_stock")
            db.commit()
        for row in df.itertuples():
            print(getattr(row, '股票代码'))
            code = getattr(row, '股票代码')
            times = getattr(row,'持有该股机构数目')
            cursor.execute("insert into t_fund_stock(`stock_code`,`times`) values('%s','%s')" % (code,times))

    db.commit()
    db.close()

def test():
    urllib3.disable_warnings()

    code = '001186'
    url = "http://fundgz.1234567.com.cn/js/%s.js" % code
    # url = "http://fundgz.1234567.com.cn/js/001186.js?rt=1463558676006"

    # 浏览器头
    headers = {'content-type': 'application/json',
               'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36'}
    req = requests.get(url, headers=headers)
    req.encoding = 'utf-8'
    content = req.text
    pattern = r'^jsonpgz\((.*)\)'

    search = re.findall(pattern, content)
    print(content)

    # for v in search:
    #     data=json.loads(v)
    #     print("基金：{}，收益率：{}".format(data['name'],data['gsz']))

    url = "http://fund.eastmoney.com/pingzhongdata/001186.js"
    req = requests.get(url, headers=headers)
    content = req.text
    print(content)


if __name__ == '__main__':
    # static_best_stock()
    # 表中基金股票统计
    fund_trace()
    # static_best_stock()
    # get_one_fund_stocks("006401")
    # static_best_stock(query_list='worst')